package com.project.dao.Impl;

import com.project.bean.*;
import com.project.dao.IInvitationDao;
import com.project.service.IUserService;
import org.springframework.stereotype.Repository;

import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

@Repository
public class InvitationDaoImpl extends BaseDao implements IInvitationDao {


    @Override
    public CutPagBean<InvitationBane> findByItem(Integer pageNo, Integer id, String name, String author, LocalDate startDate, LocalDate endDate) {

        CutPagBean<InvitationBane> cut = new CutPagBean<>();
        String str = "SELECT t.*,u.u_name,COUNT(s.pk_speakId) s FROM t_invitation  t JOIN t_user u ON u.pk_userId=t.fk_userId\n" +
                "LEFT JOIN s_speak s ON t.pk_inId =s.fk_inId WHERE t.fk_chId = ? ";
        this.setConnection();
        if (author != null && author.length() != 0) {
            str += " and u.u_name = " + "'" + author + "'";
        }
        if (name != null && name.length() != 0) {
            str += " and t.i_name = " + "'" + name + "'";
        }
        if (startDate != null) {
            str += " AND t.i_date >= '" + startDate + "'";
        }
        if (endDate != null) {
            str += " AND t.`i_date` <= '" + endDate + "'";
        }
        try {
            str += " GROUP BY t.pk_inId ";
            ps = con.prepareStatement(str + "limit ?,?");
            ps.setObject(1, id);
            ps.setObject(2, (pageNo - 1) * CutPagBean.PAGESIZE);
            ps.setObject(3, CutPagBean.PAGESIZE);
            rs = ps.executeQuery();
            while (rs.next()) {
                cut.getList().add(this.getIn());
            }
            ps = con.prepareStatement("select count(*) c from (" + str + ") t");
            ps.setObject(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                cut.setCount(rs.getInt("c"));
            }
        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            closeConnection();
        }
        return cut;
    }

    private InvitationBane getIn() {
        InvitationBane in = new InvitationBane();
        try {
            in.setId(rs.getInt("pk_inId"));
            in.setName(rs.getString("i_name"));
            UserBean userBean = new UserBean();
            userBean.setName(rs.getString("u.u_name"));
            in.setUser(userBean);
            in.setReplyNum(rs.getInt("s"));
            in.setDate(LocalDate.parse(rs.getString("t.i_date")));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return in;
    }

    @Override
    public void addInvitation(InvitationBane in) {
        this.setConnection();
        try {
            ps = con.prepareStatement("INSERT INTO t_invitation(i_name,i_date,i_content,fk_chId,fk_userId)\n" +
                    "VALUES(?,?,?,?,?)");
            ps.setObject(1, in.getName());
            ps.setObject(2, in.getDate());
            ps.setObject(3, in.getContent());
            ps.setObject(4, in.getChannel().getId());
            ps.setObject(5, in.getUser().getId());
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.closeConnection();
        }
    }

    @Override
    public InvitationBane findById(Integer id) {

        this.setConnection();
        try {
            ps = con.prepareStatement("SELECT  * FROM t_invitation WHERE pk_inId= ?");
            ps.setObject(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                InvitationBane in = new InvitationBane();
                in.setId(rs.getInt("pk_inId"));
                in.setName(rs.getString("i_name"));
                in.setDate(LocalDate.parse(rs.getString("i_date")));
                return in;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            this.closeConnection();
        }

        return null;
    }

}
